/*
Run sp_BlitzCache™ with @results = ‘Expert’ and look at the Query Hash column
for the query with multiple plans. Get that hash, and then pass it
into the following T-SQL in the WHERE clause:
*/
SELECT
	q.plan_count,
    q.distinct_plan_count,
    st.text AS query_text,
    qp.query_plan AS query_plan
FROM (SELECT query_hash,
		COUNT(DISTINCT(query_hash)) AS distinct_plan_count,
        COUNT(query_hash) AS plan_count
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
        ) AS q
        INNER JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE plan_count > 1
AND qs.query_hash = <query_hash>
ORDER BY q.PlanCount DESC;

/*
If you’d like to find more queries that sp_BlitzCache™ didn’t report
as performance problems, this query will list all of them in the cache:
*/
SELECT
	q.plan_count,
    q.distinct_plan_count,
    st.text AS QueryText,
    qp.query_plan AS query_plan
FROM    (SELECT  query_hash,
			COUNT(DISTINCT(query_hash)) AS distinct_plan_count,
            COUNT(query_hash) AS plan_count
            FROM sys.dm_exec_query_stats
            GROUP BY query_hash
        ) AS q
        INNER JOIN sys.dm_exec_query_stats qs ON q.query_hash = qs.query_hash
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE plan_count > 1
ORDER BY q.plan_count DESC;